package com.kun.peng.generator.service.impl;

import cn.hutool.core.collection.CollectionUtil;
import cn.hutool.core.util.ObjectUtil;
import cn.hutool.core.util.ZipUtil;
import com.baomidou.mybatisplus.core.toolkit.Wrappers;
import com.kun.peng.generator.domain.ColumnInfo;
import com.kun.peng.generator.domain.GenConfig;
import com.kun.peng.generator.domain.vo.TableInfo;
import com.kun.peng.exception.BadRequestException;
import com.kun.peng.generator.mapper.GenColumnInfoMapper;
import com.kun.peng.generator.utils.GenUtil;
import com.kun.peng.mybatis.base.IBaseService;
import com.kun.peng.mybatis.base.MyPage;
import com.kun.peng.utils.DbUtils;
import com.kun.peng.utils.FileUtil;
import com.kun.peng.utils.PageUtil;
import com.kun.peng.generator.service.GenColumnInfoService;
import lombok.RequiredArgsConstructor;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.apache.ibatis.session.SqlSession;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.http.HttpStatus;
import org.springframework.http.ResponseEntity;
import org.springframework.stereotype.Service;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.IOException;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.Objects;
import java.util.stream.Collectors;

/**
 * Created on 2021/11/16.
 *
 * @author xuebaopeng
 * Description
 */
@Slf4j
@Service
@RequiredArgsConstructor
public class GenColumnInfoServiceImpl extends IBaseService<GenColumnInfoMapper, ColumnInfo> implements GenColumnInfoService {

    /**
     * 获取mybatis的直接数据库连接执行sql
     */
    @Autowired
    private DbUtils dbUtils;

    @Override
    public Object getTables(String name, int[] startEnd) throws SQLException{
        // 使用预编译防止sql注入
        String like=StringUtils.isNotBlank(name)?("%" + name + "%"):"%%";
        String limit="limit "+startEnd[0]+" , "+startEnd[1];
        String sql = "select table_name ,create_time , engine, table_collation, table_comment from information_schema.tables " +
                "where table_schema = (select database()) " +
                "and table_name like "+ "\"#{table_name}\"" +" order by create_time desc "+limit+";";

        List<TableInfo> tableInfos = new ArrayList<>();
        PreparedStatement preparedStatement = null;
        try {
            ResultSet result;
            SqlSession sqlSession=dbUtils.getSqlSession();
            preparedStatement=sqlSession.getConnection().prepareStatement(sql.replace("#{table_name}",like));
            result=preparedStatement.executeQuery();
            while (result.next()) {
                TableInfo rowData =new TableInfo();
                rowData.setTableName(result.getObject(1));
                rowData.setCreateTime(result.getObject(2));
                rowData.setEngine(result.getObject(3));
                rowData.setCoding(result.getObject(4));
                rowData.setRemark(ObjectUtil.isNotEmpty(result.getObject(5)) ? result.getObject(5) : "-");
                tableInfos.add(rowData);
            }
            String countSql = "select count(1) as total from information_schema.tables " +
                    "where table_schema = (select database()) and table_name like "+"\"#{table_name}\"";
            preparedStatement=sqlSession.getConnection().prepareStatement(countSql.replace("#{table_name}",like));
            result=preparedStatement.executeQuery();
            long totalElements = 0;

            while (result.next()){
                totalElements = (long) result.getObject("total");
            }
            return PageUtil.toPage(tableInfos, totalElements);
        }catch (Exception e){
            throw e;
        }finally {
            preparedStatement.close();
            dbUtils.getSqlSession().close();
        }
    }

    @Override
    public Object getTables(MyPage<String,TableInfo> page) throws SQLException {
        // 使用预编译防止sql注入
        String like=StringUtils.isNotBlank(page.getSearch())?("%" + page.getSearch() + "%"):"%%";
        String limit="limit "+page.getCurrent()+" , "+page.getSize();
        String sql = "select table_name ,create_time , engine, table_collation, table_comment from information_schema.tables " +
                "where table_schema = (select database()) " +
                "and table_name like "+ "\"#{table_name}\"" +" order by create_time desc "+limit+";";

        List<TableInfo> tableInfos = new ArrayList<>();
        PreparedStatement preparedStatement = null;
        try {
             ResultSet result;
             SqlSession sqlSession=dbUtils.getSqlSession();
             preparedStatement=sqlSession.getConnection().prepareStatement(sql.replace("#{table_name}",like));
             result=preparedStatement.executeQuery();
             while (result.next()) {
                TableInfo rowData =new TableInfo();
                rowData.setTableName(result.getObject(1));
                rowData.setCreateTime(result.getObject(2));
                rowData.setEngine(result.getObject(3));
                rowData.setCoding(result.getObject(4));
                rowData.setRemark(ObjectUtil.isNotEmpty(result.getObject(5)) ? result.getObject(5) : "-");
                tableInfos.add(rowData);
            }
            String countSql = "select count(1) as total from information_schema.tables " +
                    "where table_schema = (select database()) and table_name like "+"\"#{table_name}\"";
            preparedStatement=sqlSession.getConnection().prepareStatement(countSql.replace("#{table_name}",like));
            result=preparedStatement.executeQuery();
            long totalElements = 0;

            while (result.next()){
                  totalElements = (long) result.getObject("total");
            }

            page.setRecords(tableInfos);
            page.setTotal(totalElements);
         }catch (Exception e){
             throw e;
         }finally {
            preparedStatement.close();
            dbUtils.getSqlSession().close();
         }
         return page;
    }

    @Override
    public List<ColumnInfo> getColumns(String tableName) throws SQLException {
        List<ColumnInfo> columnInfos = getBaseMapper().selectList((Wrappers.lambdaQuery(ColumnInfo.class).eq(ColumnInfo::getTableName,tableName)));
        if (CollectionUtil.isNotEmpty(columnInfos)) {
            return columnInfos;
        } else {
            columnInfos = query(tableName);
            saveBatch(columnInfos);
        }
        return columnInfos;
    }

    @Override
    public void sync(List<ColumnInfo> columnInfos, List<ColumnInfo> columnInfoList) {
        // 第一种情况，数据库类字段改变或者新增字段
        for (ColumnInfo columnInfo : columnInfoList) {
            // 根据字段名称查找
            List<ColumnInfo> columns = columnInfos.stream().filter(c -> c.getColumnName().equals(columnInfo.getColumnName())).collect(Collectors.toList());
            // 如果能找到，就修改部分可能被字段
            if (CollectionUtil.isNotEmpty(columns)) {
                ColumnInfo column = columns.get(0);
                column.setColumnType(columnInfo.getColumnType());
                column.setExtra(columnInfo.getExtra());
                column.setKeyType(columnInfo.getKeyType());
                if (StringUtils.isBlank(column.getRemark())) {
                    column.setRemark(columnInfo.getRemark());
                }
                 save(column);
            } else {
                // 如果找不到，则保存新字段信息
                 save(columnInfo);
            }
        }
        // 第二种情况，数据库字段删除了
        for (ColumnInfo columnInfo : columnInfos) {
            // 根据字段名称查找
            List<Long> columns = columnInfoList.stream().
                    filter(c -> c.getColumnName().equals(columnInfo.getColumnName()))
                    .map(ColumnInfo::getId).collect(Collectors.toList());
            // 如果找不到，就代表字段被删除了，则需要删除该字段
            if (CollectionUtil.isEmpty(columns)) {
               removeByIds(columns);
            }
        }
    }

    @Override
    public void save(List<ColumnInfo> columnInfos) {
        if (Objects.nonNull(columnInfos)){
            //先删除后插入
            getBaseMapper().delete(Wrappers.lambdaQuery(ColumnInfo.class).eq(ColumnInfo::getTableName,columnInfos.get(0).getTableName()));
            saveBatch(columnInfos);
        }
    }

    @Override
    public void generator(GenConfig genConfig, List<ColumnInfo> columns) {
        if (genConfig.getId() == null) {
            throw new BadRequestException("请先配置生成器");
        }
        try {
            GenUtil.generatorCode(columns, genConfig);
        } catch (IOException e) {
            log.error(e.getMessage(), e);
            throw new BadRequestException("生成失败，请手动处理已生成的文件");
        }
    }

    @Override
    public ResponseEntity<Object> preview(GenConfig genConfig, List<ColumnInfo> columns) {
        if (genConfig.getId() == null) {
            throw new BadRequestException("请先配置生成器");
        }
        List<Map<String, Object>> genList = GenUtil.preview(columns, genConfig);
        return new ResponseEntity<>(genList, HttpStatus.OK);
    }

    @Override
    public void download(GenConfig genConfig, List<ColumnInfo> columns, HttpServletRequest request, HttpServletResponse response) {
        if (genConfig.getId() == null) {
            throw new BadRequestException("请先配置生成器");
        }
        try {
            File file = new File(GenUtil.download(columns, genConfig));
            String zipPath = file.getPath() + ".zip";
            ZipUtil.zip(file.getPath(), zipPath);
            FileUtil.downloadFile(request, response, new File(zipPath), true);
        } catch (IOException e) {
            throw new BadRequestException("打包失败");
        }
    }

    @Override
    public List<ColumnInfo> query(String tableName) throws SQLException {
        // 使用预编译防止sql注入
        String sql = "select column_name, is_nullable, data_type, column_comment, column_key, extra from information_schema.columns " +
                "where table_name = "+"\'#{table_name}\'" +" and table_schema = (select database()) order by ordinal_position";

        PreparedStatement preparedStatement = null;
        List<ColumnInfo> columnInfos = new ArrayList<>();
        try {
            ResultSet result;
            preparedStatement= dbUtils.getSqlSession().getConnection().prepareStatement(sql.replace("#{table_name}",tableName));
            result=preparedStatement.executeQuery();

            while (result.next()){
                columnInfos.add(
                        new ColumnInfo(
                                tableName,
                                result.getObject(1).toString(),
                                "NO".equals(result.getObject(2)),
                                result.getObject(3).toString(),
                                ObjectUtil.isNotNull(result.getObject(4))? result.getObject(4).toString() : null,
                                ObjectUtil.isNotNull(result.getObject(5))? result.getObject(5).toString() : null,
                                ObjectUtil.isNotNull(result.getObject(6)) ? result.getObject(6).toString() : null)
                );
            }
            return columnInfos;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            preparedStatement.close();
            dbUtils.getSqlSession().getConnection().close();
        }
        return columnInfos;
    }
}
